﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace UniMana.Data
{
    public static class Training_Student_ClassData
    {

        /// <summary>
        /// Lấy danh sách ngành học của sinh viên một lớp học phần nào đó
        /// </summary>
        /// <param name="t1"></param>
        /// <param name="t2"></param>
        /// <param name="t3"></param>
        /// <param name="subjectClassKey">Key lớp học phần</param>
        /// <returns></returns>
        public static IQueryable GetMajorList(this Table<Training_Student_Class> t1, Table<Training_Student_Subject_Class> t2,
                                                Table<Training_Student> t3, int subjectClassKey)
        {
            var major = (from stc in t1
                           join
                                 st in t3 on stc.Training_StudentKey equals st.Training_StudentKey
                           join
                                 sts in t2 on st.Training_StudentKey equals sts.Training_StudentKey
                           where sts.Training_Subject_ClassKey == subjectClassKey
                           orderby st.SureName
                           select new
                           {
                               sts.Training_Subject_ClassKey,
                               stc.Catalogue_MajorKey
                           }).Distinct();
            return major;
        }

        /// <summary>
        /// (Thi tot nghiep) Danh sach sinh vien du dieu kien du thi tot nghiep
        /// </summary>
        /// <param name="tStudentClass"></param>
        /// <param name="tStudent"></param>
        /// <param name="tDepartment"></param>
        /// <param name="tMajor"></param>
        /// <param name="tProgram"></param>
        /// <param name="iDepartmentKey"></param>
        /// <param name="iLevelKey"></param>
        /// <param name="iMajorKey"></param>
        /// <param name="iCourseKey"></param>
        /// <param name="fMinScorePass"></param>
        /// <param name="bEngInfo"></param>
        /// <param name="bFee"></param>
        /// <param name="bThesis"></param>
        /// <param name="bExam"></param>
        /// <returns></returns>
        public static IQueryable GetStudentList(this Table<Training_Student_Class> tStudentClass, Table<Training_Student> tStudent,
                                                Table<Catalogue_Department> tDepartment, Table<Catalogue_Major> tMajor,
                                                Table<Training_TrainingProgram> tProgram,
                                                int iDepartmentKey, int iLevelKey, int iMajorKey, int iCourseKey,
                                                double fMinScorePass, bool bEngInfo = false, bool bFee = false,
                                                bool bThesis = false, bool bExam = false)
        {
            var l = from studentClass in tStudentClass
                    from student in tStudent
                    from department in tDepartment
                    from major in tMajor
                    from program in tProgram

                    where studentClass.Training_StudentKey == student.Training_StudentKey
                    where studentClass.Catalogue_MajorKey == major.Catalogue_MajorKey
                    where major.Catalogue_DepartmentKey == department.Catalogue_DepartmentKey
                    where studentClass.Training_TrainingProgramKey == program.Training_TrainingProgramKey

                    //where studentClass.AverageScoreAccumulate != null && studentClass.AverageScoreAccumulate >= fMinScorePass//diem trung binh tich luy
                    //where studentClass.TotalCreditAccumulate != null && studentClass.TotalCreditAccumulate >= program.TotalCredits//tin chi tich luy
                    //where studentClass.Graduated == false || studentClass.Graduated == null//SV chua tot nghiep
                    where studentClass.Catalogue_CourseKey == iCourseKey// filter all course of Student less than iCourseKey
                    where studentClass.Catalogue_LevelKey == iLevelKey
                    where department.Catalogue_DepartmentKey == iDepartmentKey
                    where major.Catalogue_MajorKey == iMajorKey

                    orderby studentClass.ID

                    select new
                    {
                        studentClass.Training_StudentKey,
                        studentClass.ID,
                        student.LastName,
                        student.SureName,
                        Birthday = student.Birthday != null ? student.Birthday.ToString() : student.ExceptionBirthday,
                        Gender = student.Gender == true ? "Nữ" : "Nam",
                        student.PlaceOfBirth,
                        studentClass.AverageScoreAccumulate,
                        studentClass.TotalCreditAccumulate,
                        EngInfoCertificate = studentClass.EngInfoCertificate == null ? false : studentClass.EngInfoCertificate,
                        Fee = studentClass.Fee == null ? false : studentClass.Fee,
                        GraduateThesis = studentClass.GraduateThesis == null ? false : studentClass.GraduateThesis,
                        GraduateExam = studentClass.GraduateExam == null ? false : studentClass.GraduateExam,
                        studentClass.Training_TrainingProgramKey
                    };
            if (bEngInfo == true)
            {
                l = l.Where(p => p.EngInfoCertificate == true);
            }
            if (bFee == true)
            {
                l = l.Where(p => p.Fee == true);
            }
            if (bThesis == true)
            {
                l = l.Where(p => p.GraduateThesis == true);
            }
            if (bExam == true)
            {
                l = l.Where(p => p.GraduateExam == true);
            }
            if (bThesis == false && bExam == false)
            {
                l = l.Where(p => p.GraduateExam == false && p.GraduateThesis == false);
            }
            return l;
        }

        #region [Chi]
        /// <summary>
        /// danh sách Mã sinh viên học môn
        /// </summary>
        /// <param name="subjectClassKey"></param>
        /// <param name="student_Class"></param>
        /// <param name="student"></param>
        /// <param name="student_Subject_Class"></param>
        /// <returns></returns>
        public static IQueryable<Training_Student_Class>
            GetStudentID(this Table<Training_Student_Class> student_Class,
                            Table<Training_Student_Subject_Class> student_Subject_Class,
                            Table<Training_Student> student,
                            int subjectClassKey)
        {
            var Student = from stc in student_Class
                          join st in student on stc.Training_StudentKey equals st.Training_StudentKey
                          join sts in student_Subject_Class on st.Training_StudentKey equals sts.Training_StudentKey
                          where sts.Training_Subject_ClassKey == subjectClassKey
                          select stc;
            return Student.Distinct();
        }
        #endregion

        #region [An]
        /// <summary>
        /// Trả về mã số sinh viên cao nhất cho ngành và khóa học
        /// </summary>
        /// <param name="nTraining_Student_Class"></param>
        /// <param name="m_iMajorKey"></param>
        /// <param name="m_iCourseKey"></param>
        /// <param name="m_strIDFirst"></param>
        /// <returns></returns>

        public static string ReturnStudentIDMax(this Table<Training_Student_Class> nTraining_Student_Class,
            int m_iMajorKey,
            int m_iCourseKey,
            string m_strIDFirst)
        {
            if (m_iCourseKey >= 5)
            {
                var values = (from t in nTraining_Student_Class
                              where t.Catalogue_MajorKey == m_iMajorKey &&
                              t.Catalogue_CourseKey == m_iCourseKey &&
                              t.ID.StartsWith(m_strIDFirst)
                              orderby t.ID descending
                              select new
                              {
                                  t.ID
                              }).FirstOrDefault();
                return values.ID.ToString();
            }
            else
            {
                var values = (from t in nTraining_Student_Class
                              where t.Catalogue_MajorKey == m_iMajorKey &&
                              t.Catalogue_CourseKey == m_iCourseKey
                              orderby t.ID descending
                              select new
                              {
                                  t.ID
                              }).FirstOrDefault();
                return values.ID.ToString();
            }
        }

        /// <summary>
        /// Lấy thông tin với mã sinh viên
        /// </summary>
        /// <param name="nTraining_Student_Class"></param>
        /// <param name="m_strStudentID"></param>
        /// <returns></returns>

        public static IQueryable GetByStudentID(this Table<Training_Student_Class> nTraining_Student_Class,
            string m_strStudentID)
        {
            var values = from t in nTraining_Student_Class
                         where t.ID == m_strStudentID
                         select new { t.ID };
            return values.Distinct();
        }

        /// <summary>
        /// Đếm số sinh viên trong lớp
        /// </summary>
        /// <param name="nTraining_Student_Class"></param>
        /// <param name="m_iTraining_ClassKey"></param>
        /// <returns></returns>

        public static int ReturnStudentNumberInClass(this Table<Training_Student_Class> nTraining_Student_Class,
            int m_iTraining_ClassKey)
        {
            var values = from t in
                             (from t in nTraining_Student_Class
                              where
                                t.Training_ClassKey == m_iTraining_ClassKey
                              select new
                              {
                                  t.ID,
                                  Dummy = "x"
                              })
                         group t by new { t.Dummy } into g
                         select new
                         {
                             numbercount = g.Count()
                         };
            try
            {
                int num = values.FirstOrDefault().numbercount;
                return num;
            }
            catch { return 0; }
        }
        #endregion
    }
}
